﻿Imports MICROFORMA.BEL
Imports MICROFORMA.DIL
Imports Microsoft.Practices.EnterpriseLibrary.Data
Imports System.Data.Common

Public Class UsuarioDA
    Implements DIL.UsuarioDI

    Public Function List() As BEL.UsuarioBECollection Implements DIL.UsuarioDI.List

    End Function
    Function Add(ByVal _usuario_BE As UsuarioBE, ByVal _roles As ArrayList, ByVal _groups As ArrayList, ByVal _repository As ArrayList) As Int16 Implements DIL.UsuarioDI.Add
        Dim idUsuario As Int16 = 0

        Dim db As Database = DatabaseFactory.CreateDatabase()

        Dim query As String = "Insert into usuario (Nombre,Login,Password,Email,TipoAcceso,Habilitado,FechaCreacion,Estado)" _
                    & "Values ('" & _usuario_BE.Nombre & "','" & _usuario_BE.Login & "','" & _usuario_BE.Password & "','" & _usuario_BE.Email _
                    & "','" & _usuario_BE.TipoAcceso & "','" & _usuario_BE.Habilitado & "', getdate() ,'" & _usuario_BE.Estado & "') " _
        & " SELECT @@IDENTITY"


        Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim o_Cnx As DbConnection = db.CreateConnection()

        o_Cnx.Open()

        Dim o_Transaction As DbTransaction = o_Cnx.BeginTransaction

        Try


            idUsuario = db.ExecuteScalar(o_dbCommand, o_Transaction)

            For index As Integer = 0 To _roles.Count - 1
                query = "insert into RolUsuario (idUsuario,idRol,FechaCreacion) " & _
                        "values ('" & idUsuario & "','" & _roles(index) & "'," & "getdate()" & ")"

                db.ExecuteNonQuery(o_Transaction, CommandType.Text, query)
            Next

            For index As Integer = 0 To _groups.Count - 1
                query = "insert into GrupoUsuarios (idUsuario,idGrupo,FechaCreacion) " & _
                        "values ('" & idUsuario & "','" & _groups(index) & "'," & "getdate()" & ")"

                db.ExecuteNonQuery(o_Transaction, CommandType.Text, query)
            Next

            For index As Integer = 0 To _repository.Count - 1
                query = "insert into Acceso (idRepositorio,Estado,idUsuario) " & _
                  "values ('" & _repository(index) & "','" & 0 & "','" & idUsuario & "')"

                db.ExecuteNonQuery(o_Transaction, CommandType.Text, query)
            Next

            o_Transaction.Commit()

        Catch ex As Exception
            o_Transaction.Rollback()
            Return ex.Message.ToString
        Finally
            o_Cnx.Close()
        End Try

        Return idUsuario

    End Function
    Public Function GetFindUser(ByVal strTexto As String) As System.Data.DataTable Implements DIL.UsuarioDI.GetFindUser

        Dim db As Database = DatabaseFactory.CreateDatabase()

        'Dim query As String = "SELECT IdUsuario, Login, Nombre, CASE Usuario.Estado WHEN '0' THEN 'Activo'  WHEN '1' THEN 'Suspendido' END AS EstadoDescripcion  FROM Usuario where Estado='0' " ' and (Nombre like '%" & strTexto & "%' or Login like '%" & strTexto & "%')"
        Dim query As String = "SELECT * " _
                            & ", CASE Usuario.Estado WHEN '0' THEN 'Activo'  WHEN '1' THEN 'Suspendido' END AS EstadoDescripcion " _
                            & ", CASE Usuario.TipoAcceso WHEN 's' THEN 'ScanFlow'  WHEN 'w' THEN 'Windows' END AS TipoAccessoDescripcion  FROM Usuario  " 'where Estado='0' and (Nombre like '%" & strTexto & "%' or Login like '%" & strTexto & "%')"

        Dim dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim productsDataSet As DataSet = Nothing

        productsDataSet = db.ExecuteDataSet(dbCommand)

        Return productsDataSet.Tables(0)

    End Function
    Public Function Update(ByVal _usuario_BE As BEL.UsuarioBE) As String Implements DIL.UsuarioDI.Update

        Dim db As Database = DatabaseFactory.CreateDatabase()


        Dim query As String = "Update usuario set Nombre='" & _
                             _usuario_BE.Nombre & "', Login='" & _usuario_BE.Login & "', Password='" & _
                             _usuario_BE.Password & "', Email='" & _usuario_BE.Email & "', TipoAcceso='" & _
                             _usuario_BE.TipoAcceso & "', Habilitado='" & _usuario_BE.Habilitado & "', Estado='" & _
                              _usuario_BE.Estado & "' where IdUsuario='" & _usuario_BE.IdUsuario & "'"


        Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim o_Cnx As DbConnection = db.CreateConnection()

        o_Cnx.Open()

        Dim o_Transaction As DbTransaction = o_Cnx.BeginTransaction

        Try

            db.ExecuteNonQuery(o_dbCommand, o_Transaction)

            o_Transaction.Commit()

            Return "OK"
        Catch ex As Exception
            o_Transaction.Rollback()
            Return ex.Message.ToString
        Finally
            o_Cnx.Close()
        End Try


    End Function
    Public Function EnableDisableUser(ByVal idUsuario As String, ByVal strEstado As String) As String Implements DIL.UsuarioDI.EnableDisableUser
        Dim db As Database = DatabaseFactory.CreateDatabase()


        Dim query As String = "Update usuario set Habilitado='" & _
                             strEstado & "', Estado='" & strEstado & "' where IdUsuario='" & idUsuario & "'"


        Dim o_dbCommand As DbCommand = db.GetSqlStringCommand(query)

        Dim o_Cnx As DbConnection = db.CreateConnection()

        o_Cnx.Open()

        Dim o_Transaction As DbTransaction = o_Cnx.BeginTransaction

        Try

            db.ExecuteNonQuery(o_dbCommand, o_Transaction)

            o_Transaction.Commit()

            Return "OK"
        Catch ex As Exception
            o_Transaction.Rollback()
            Return ex.Message.ToString
        Finally
            o_Cnx.Close()
        End Try
    End Function

End Class
